package com.wlanauth.web.action;

import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import net.sf.jxls.transformer.XLSTransformer;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.xwork.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts2.ServletActionContext;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import com.wlanauth.core.criteria.GeneralCriteria;
import com.wlanauth.core.criteria.GeneralCriteriaResult;
import com.wlanauth.domain.manage.User;
import com.wlanauth.vo.UserHisVO;
import com.wlanauth.vo.UserVO;
import com.wlanauth.web.core.BaseActionSupport;
import com.wlanauth.web.core.JsonResult;
import com.wlanauth.web.util.RadiusUtils;
/**
 * 导出Excel
 * @author zz
 * @date 2012-6-11
 * @email charles.zz.life@gmail.com
 */
public class ExcelAction extends BaseActionSupport {
	
	private static final long serialVersionUID = 1L;
	private static SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
	
	private String templateDir = "/templates"; //模板目录
	private String userTplFilename = "tpl_user.xls"; //模板名
	private String userhisTplFilename = "tpl_user.xls"; //模板名
	
	private String tempPath;//生成Excel的临时目录
	
	private User user;
	
	private ExcelConfig config;//导出配置
	
	public static boolean cleanTaskRunning = false;//清理
	
	private long keepTime = 5*60*1000;//存放时长;默认5分钟
	
	private String userIds;//导出用户 ID(以,分隔);
	
	private static Map<String,String> colNames = new HashMap<String,String>();
	static {
		colNames.put("username", "用户名");
		colNames.put("puk", "PUK码");
		colNames.put("bindIP", "绑定IP");
		colNames.put("expiredTime", "有效期");
		colNames.put("sumInputOctets", "上行流量(KB)");
		colNames.put("sumOutputOctets", "下行流量(KB)");
		colNames.put("sumOctets", "已用流量(M)");
		colNames.put("maxOctets", "额定流量(M)");
		colNames.put("remainOctets", "剩余流量(M)");
		colNames.put("actualFee", "额定金额(元)");
		colNames.put("remainFee", "剩余金额(元)");
		colNames.put("batchId", "批次号");
		colNames.put("lastAcctStartTime", "最后登陆时间");
		
	}
	public ExcelAction(){
		
	}

	public String getTemplateDir() {
		return templateDir;
	}
	public void setTemplateDir(String templateDir) {
		this.templateDir = templateDir;
	}
	public String getUserTplFilename() {
		return userTplFilename;
	}
	public void setUserTplFilename(String userTplFilename) {
		this.userTplFilename = userTplFilename;
	}
	public String getUserhisTplFilename() {
		return userhisTplFilename;
	}
	public void setUserhisTplFilename(String userhisTplFilename) {
		this.userhisTplFilename = userhisTplFilename;
	}
	
	/**
	 * 导出在网用户
	 * @return
	 * @throws Exception
	 */
	public String exportUser() throws Exception{
		if(this.config.getPropertyNames()==null||this.config.getPropertyNames().size()<1){
			this.jsonResult = new JsonResult(this,"导出列不能为空",false);
			return this.actionJsonResult();
		}
		GeneralCriteria criteria =  new GeneralCriteria(UserVO.class);
		criteria.setDataOnly(!config.isShowTotal());
		
		if("SELECTED".equals(config.getExportType())||"PAGE".equals(config.getExportType())){
			String[] ids = this.userIds.split(",");
			if(ids.length<1){
				this.jsonResult = new JsonResult(this,"ID不能为空",false);
				return this.actionJsonResult();
			}
			criteria.add(Restrictions.in("id", ids));
		} else if ("RANGE".equals(config.getExportType())){
			criteria.setStart(config.getStart());
			criteria.setLimit(config.getLimit());
		} 
		
		criteria.addOrder(Order.desc("joinTime"));
		criteria.addOrder(Order.desc("lastAcctStartTime"));
		GeneralCriteriaResult<UserVO> result = criteria.executeQuery();
		//转换
		List<String> cols = new ArrayList<String>();
		for(String propertyName:this.config.getPropertyNames()){
			if(StringUtils.isNotBlank(propertyName)){
				cols.add(propertyName);
			}
		}
		List<Map<String,Object>> vals = new ArrayList<Map<String,Object>>();
		if(result.getData()!=null){
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
			SimpleDateFormat timeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			for(UserVO vo:result.getData()){
				vo.setSumInputOctets(RadiusUtils.getViewOct(vo.getSumInputOctets()));
				vo.setSumOutputOctets(RadiusUtils.getViewOct(vo.getSumOutputOctets()));
				
				BigDecimal sumOctets = RadiusUtils.getViewOct(vo.getSumInputOctets().add(vo.getSumOutputOctets()));
				Map<String,Object> map = new HashMap<String,Object>();
				for(String key:cols){
					if(key.equals("sumOctets")){
						map.put("sumOctets",sumOctets==null?0:sumOctets.longValue()/1024/1024);
					} else if(key.equals("remainOctets")){
						map.put("remainOctets", vo.getMaxOctets()-(sumOctets==null?0:sumOctets.longValue()/1024/1024));
					} else if(key.equals("sumInputOctets")){
						map.put("sumInputOctets", vo.getSumInputOctets()==null?0:vo.getSumInputOctets().longValue()/1024);
					} else if(key.equals("sumOutputOctets")){
						map.put("sumOutputOctets", vo.getSumOutputOctets()==null?0:vo.getSumOutputOctets().longValue()/1024);
					} else if(key.equals("remainFee")){
						double ratio = vo.getActualFee()/(vo.getMaxOctets()*1024);
						map.put("remainFee",vo.getActualFee()-RadiusUtils.getFee(sumOctets==null?0:sumOctets.longValue(),ratio));
					} else if(key.equals("expiredTime")){
						map.put("expiredTime", dateFormat.format(vo.getExpiredTime()));
					} else if(key.equals("lastAcctStartTime")){
						map.put("lastAcctStartTime", vo.getLastAcctStartTime()==null?"":timeFormat.format(vo.getLastAcctStartTime()));
					} else {
						map.put(key, BeanUtils.getProperty(vo, key));
					}
				}
				vals.add(map);
			}
		}
		Map<String,Object> root = new HashMap<String,Object>();
		root.put("vals", vals);
		root.put("cols", cols);
		root.put("colNames", colNames);
		root.put("config", this.config);
		root.put("total", result.getTotal());
		String filename = "User-"+df.format(new Date())+".xls";
		OutputStream writer = null;
		try {
			XLSTransformer transformer = new XLSTransformer();
			String rootPath = ServletActionContext.getServletContext().getRealPath("/");
			String templateDirPath = rootPath+this.getTemplateDir();
			HSSFWorkbook wb = this.openWorkbook(new File(templateDirPath,this.userTplFilename));
			
			transformer.transformWorkbook(wb, root);
			
			//writer = this.getExcelResponse(filename).getOutputStream();
			writer = new FileOutputStream(new File(getExportTempDir(),filename));
			wb.write(writer);
			this.jsonResult = new JsonResult(this,tempPath+filename,true);
		} finally {
			if(writer != null){
				try { writer.close();} catch (Exception e){}
			}
		}
		this.clean();
		return this.actionJsonResult();
	}
	
	private File getExportTempDir(){
		String rootPath = ServletActionContext.getServletContext().getRealPath("/");
		File dir = new File(rootPath+tempPath);
		if(!dir.exists()){
			dir.mkdirs();
		}
		return dir;
	}

	/** 
     * 打开一个工作簿 
     * @param file  工作簿文件 
     * @return 
     */  
    private HSSFWorkbook openWorkbook(File file){  
        InputStream in = null;  
        HSSFWorkbook wb = null;  
        try {  
            in = new FileInputStream(file);  
            wb = new HSSFWorkbook(in);  
        } catch (Exception e) {  
            throw new RuntimeException("File" + file.getPath() + " not found:" + e.getMessage());  
        } finally {  
            try {  
                in.close();  
            } catch (Exception e) {  
              
            }  
        }  
        return wb;  
    }  
    /**
     * 导出历名用户信息(与在网用户类似)
     * @return
     * @throws Exception
     */
	public String exportUserHis() throws Exception{
		
		GeneralCriteria criteria =  new GeneralCriteria(UserHisVO.class);
		criteria.setDataOnly(!config.isShowTotal());
		if("SELECTED".equals(config.getExportType())||"PAGE".equals(config.getExportType())){
			String[] ids = this.userIds.split(",");
			if(ids.length<1){
				this.jsonResult = new JsonResult(this,"ID不能为空",false);
				return this.actionJsonResult();
			}
			criteria.add(Restrictions.in("id", ids));
		} else if ("RANGE".equals(config.getExportType())){
			criteria.setStart(config.getStart());
			criteria.setLimit(config.getLimit());
		} 
		criteria.addOrder(Order.desc("joinTime"));
		criteria.addOrder(Order.desc("lastAcctStartTime"));
		GeneralCriteriaResult<UserHisVO> result = criteria.executeQuery();
		//转换
		List<String> cols = new ArrayList<String>();
		for(String propertyName:this.config.getPropertyNames()){
			if(StringUtils.isNotBlank(propertyName)){
				cols.add(propertyName);
			}
		}
		List<Map<String,Object>> vals = new ArrayList<Map<String,Object>>();
		if(result.getData()!=null){
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
			SimpleDateFormat timeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			for(UserHisVO vo:result.getData()){
				vo.setSumInputOctets(RadiusUtils.getViewOct(vo.getSumInputOctets()));
				vo.setSumOutputOctets(RadiusUtils.getViewOct(vo.getSumOutputOctets()));
				
				BigDecimal sumOctets = RadiusUtils.getViewOct(vo.getSumInputOctets().add(vo.getSumOutputOctets()));
				Map<String,Object> map = new HashMap<String,Object>();
				for(String key:cols){
					if(key.equals("sumOctets")){
						map.put("sumOctets",sumOctets==null?0:sumOctets.longValue()/1024/1024);
					} else if(key.equals("remainOctets")){
						map.put("remainOctets", vo.getMaxOctets()-(sumOctets==null?0:sumOctets.longValue()/1024/1024));
					} else if(key.equals("sumInputOctets")){
						map.put("sumInputOctets", vo.getSumInputOctets()==null?0:vo.getSumInputOctets().longValue()/1024);
					} else if(key.equals("sumOutputOctets")){
						map.put("sumOutputOctets", vo.getSumOutputOctets()==null?0:vo.getSumOutputOctets().longValue()/1024);
					} else if(key.equals("remainFee")){
						double ratio = vo.getActualFee()/(vo.getMaxOctets()*1024);
						map.put("remainFee",vo.getActualFee()-RadiusUtils.getFee(sumOctets==null?0:sumOctets.longValue(),ratio));
					} else if(key.equals("expiredTime")){
						map.put("expiredTime", dateFormat.format(vo.getExpiredTime()));
					} else if(key.equals("lastAcctStartTime")){
						map.put("lastAcctStartTime", vo.getLastAcctStartTime()==null?"":timeFormat.format(vo.getLastAcctStartTime()));
					} else {
						map.put(key, BeanUtils.getProperty(vo, key));
					}
				}
				vals.add(map);
			}
		}
		Map<String,Object> root = new HashMap<String,Object>();
		root.put("vals", vals);
		root.put("cols", cols);
		root.put("colNames", colNames);
		root.put("config", this.config);
		root.put("total", result.getTotal());
		
		String filename = "UserHis_"+df.format(new Date())+".xls";
		OutputStream writer = null;
		try {
			XLSTransformer transformer = new XLSTransformer();
			String rootPath = ServletActionContext.getServletContext().getRealPath("/");
			String templateDirPath = rootPath+this.getTemplateDir();
			HSSFWorkbook wb = this.openWorkbook(new File(templateDirPath,this.userhisTplFilename));
			
			transformer.transformWorkbook(wb, root);
			writer = new FileOutputStream(new File(getExportTempDir(),filename));
			wb.write(writer);
			this.jsonResult = new JsonResult(this,tempPath+filename,true);
		} finally {
			if(writer != null){
				try { writer.close();} catch (Exception e){}
			}
		}
		this.clean();
		return this.actionJsonResult();
	}

	
	public HttpServletResponse getExcelResponse(String name) throws UnsupportedEncodingException{
//		this.response.setCharacterEncoding("ISO-8859-1");
		this.response.setContentType("application/msexcel;charset=GBK");
		this.response.addHeader("Content-Disposition","attachment;filename=" + new String(name.getBytes("GBK"),"ISO-8859-1"));
		return this.response;
		
	}

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}
	
	
	public ExcelConfig getConfig() {
		return config;
	}
	public void setConfig(ExcelConfig config) {
		this.config = config;
	}


	public void setTempPath(String tempPath) {
		this.tempPath = tempPath;
	}


	public void setKeepTime(long keepTime) {
		this.keepTime = keepTime;
	}

	private void clean(){
		//作文件清理工作
		if(!cleanTaskRunning){
			final File tempDir = this.getExportTempDir();
			Thread cleanThread = new Thread(new Runnable(){

				public void run() {
					while(true){
						File[] files = tempDir.listFiles(new FileFilter(){

							public boolean accept(File file) {
								if(file.getName().endsWith(".xls")&&
										(System.currentTimeMillis()-file.lastModified())>=keepTime){
									return true;
								}
								return false;
							}
							
						});
						for(File f:files){
							if(f.exists()){
								f.delete();
							}
						}
						try {
							Thread.sleep(10*1000);
						} catch (InterruptedException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}//10s种扫描一次
					}
				}
				
			});
			cleanThread.setDaemon(true);
			cleanThread.start();
			cleanTaskRunning = true;
		}
		
		
	}
	public String getUserIds() {
		return userIds;
	}

	public void setUserIds(String userIds) {
		this.userIds = userIds;
	}
	/**
	 * 导出配置
	 * @author zz
	 *
	 */
	public static class ExcelConfig{
		private List<String> propertyNames;
		private boolean showTotal;
		private int start;
		private int limit;
		/**
		 * 导出类型:SELECTED,PAGE,ALL,RANGE
		 */
		private String exportType ;
		
		public List<String> getPropertyNames() {
			
			return propertyNames;
		}
		public void setPropertyNames(List<String> propertyNames) {
			this.propertyNames = propertyNames;
		}
		public boolean isShowTotal() {
			return showTotal;
		}
		public void setShowTotal(boolean showTotal) {
			this.showTotal = showTotal;
		}
		public int getStart() {
			return start;
		}
		public void setStart(Integer start) {
			this.start = start;
		}
		public int getLimit() {
			return limit;
		}
		public void setLimit(int limit) {
			this.limit = limit;
		}
		
		public void setStart(int start) {
			this.start = start;
		}
		public String getExportType() {
			return exportType;
		}
		public void setExportType(String exportType) {
			this.exportType = exportType;
		}
		
		
		
	}
	
}
